﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using DTO;
namespace DAO
{
    public class BinhGasDAO
    {
        public static DataTable Tim( int loaiGas)
        {
            try
            {
                String strSql = "select* from BinhGas where LoaiGas=" + loaiGas;
                OleDbConnection conn = DataAccess.OpenOleConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);                
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception ex) { throw ex; }
        }
        public static DataTable Tim(OleDbTransaction trans,int loaiGas)
        {
            try
            {
                String strSql = "select* from BinhGas where LoaiGas=" + loaiGas;
                OleDbConnection conn = DataAccess.OpenOleConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                da.SelectCommand.Transaction = trans;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex) { throw ex; }
        }
        public static int GetSoLuong(int loaiGas)
        {
            try
            {
                String strSql = "select sum(SoLuong) from BinhGas where LoaiGas=" + loaiGas;
                OleDbConnection conn = DataAccess.OpenOleConnection();
                OleDbCommand com = new OleDbCommand(strSql, conn);
                int soLuong = int.Parse(com.ExecuteScalar().ToString());
                conn.Close();
                return soLuong;
            }
            catch (Exception ex) { throw; }
        }
        public static void CapNhatSoLuong(int loaiGas, int soLuong)
        {
            try
            {
                DataTable dt = Tim(loaiGas);
                if (dt.Rows.Count == 0)
                {
                    BinhGasDTO binhGas = new BinhGasDTO();
                    binhGas.LoaiGas = loaiGas;
                    binhGas.SoLuong = 0;
                    Them(binhGas);
                }
                OleDbConnection conn = DataAccess.OpenOleConnection();                                
                String strSql = "update BinhGas set SoLuong=Soluong+? where LoaiGas=?";
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                
                com.Parameters["@SoLuong"].Value = soLuong;
                com.Parameters["@LoaiGas"].Value = loaiGas;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { }
        }
        public static void CapNhatSoLuong(OleDbTransaction tran, int loaiGas, int soLuong)
        {
            try
            {
                OleDbConnection conn = DataAccess.OpenOleConnection();
                DataTable dt = Tim(tran, loaiGas);
                if (dt.Rows.Count == 0)
                {
                    BinhGasDTO binhGas = new BinhGasDTO();
                    binhGas.LoaiGas = loaiGas;
                    binhGas.SoLuong = 0;
                    Them(tran, binhGas);
                }
                String strSql = "update BinhGas set SoLuong=Soluong+? where LoaiGas=?";
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);

                com.Parameters["@SoLuong"].Value = soLuong;
                com.Parameters["@LoaiGas"].Value = loaiGas;
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { throw; }
        }
        public static void Them(BinhGasDTO binhGas)
        {
            try
            {
                String strSql = "insert into BinhGas(LoaiGas, SoLuong) values(?,?)";
                OleDbConnection conn = DataAccess.OpenOleConnection();
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);

                com.Parameters["@LoaiGas"].Value = binhGas.LoaiGas;
                com.Parameters["@SoLuong"].Value = binhGas.SoLuong;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { throw; }
        }
        public static void Them(OleDbTransaction tran, BinhGasDTO binhGas)
        {
            String strSql = "insert into BinhGas(LoaiGas, SoLuong) values(?,?)";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbCommand com = new OleDbCommand(strSql, conn);
            try
            {                
                com.Transaction = tran;
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);

                com.Parameters["@LoaiGas"].Value = binhGas.LoaiGas;
                com.Parameters["@SoLuong"].Value = binhGas.SoLuong;
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { throw; }
        }
        public static void TangSoLuong(int loaiGas, int soLuong)
        {
            try
            {
                CapNhatSoLuong(loaiGas, soLuong);
            }
            catch (Exception ex) { throw; }
        }
        public static void TangSoLuong(OleDbTransaction tran, int loaiGas, int soLuong)
        {
            try
            {
                CapNhatSoLuong(tran, loaiGas, soLuong);
            }
            catch (Exception ex) { throw; }
        }
        public static void GiamSoLuong(int loaiGas, int soLuong)
        {
            try
            {
                CapNhatSoLuong(loaiGas, -soLuong);
            }
            catch (Exception ex) { throw; }
        }

        public static void GiamSoLuong(OleDbTransaction tran, int loaiGas, int soLuong)
        {
            try
            {
                CapNhatSoLuong(tran, loaiGas, -soLuong);
            }
            catch (Exception ex) { throw; }
        }
    }
}
